package top.jnaw.jee.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.DbPro;
import com.jfinal.plugin.activerecord.Record;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import top.jnaw.jee.platform.Consts;

public class Excel {

  public static final int NONE = -1;
  public static final int NUMERIC = 1;
  public static final int STRING = 2;
  public static final int FORMULA = 3;
  public static final int BLANK = 4;
  public static final int BOOLEAN = 5;
  public static final int ERROR = 6;
  public static final int DATE = 10;
  public static final int DATETIME = 11;

  private static final int VARCHAR_SIZE = 1024;
  private static final char[] N26 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();

  private Workbook workbook;

  private JSONArray data;
  private ArrayList<ArrayList<Title>> titles;

  private JSONArray picked;
  private Selection selection;

  private boolean hasParsed;

  private CellStyle dateStyle;
  private CellStyle dateTimeStyle;

  public Excel() {
    init(true);
  }

  public Excel(File file) throws Exception {
    try {
      init(false);

      String name = file.getName().toLowerCase();
      InputStream in = new FileInputStream(file);

      if (name.toLowerCase().endsWith(ext.xls.toString())) {
        workbook = new HSSFWorkbook(in);
      } else if (name.toLowerCase().endsWith(ext.xlsx.toString())) {
        workbook = new XSSFWorkbook(in);
      }

    } catch (Exception e) {
      throw new Exception("File not support");
    }

    parse();
  }

  public Excel(DbPro db, String table) {
    init(true);
    parse(db, table, null);
  }

  public Excel(DbPro db, String table, String select, Object... paras) {
    init(true);
    parse(db, table, select, paras);
  }

  public void toDB(DbPro db, String table) {
    toDB(db, table, true);
  }

  public void toDB(DbPro db, String table, boolean overwrite) {
    StringBuilder sqlCreate = new StringBuilder();
    sqlCreate.append("CREATE TABLE IF NOT EXISTS `").append(table).append("` (")
        .append("`id` int(11) NOT NULL AUTO_INCREMENT, ");

    // [Neo] .TODO
    JSONArray records = data.getJSONArray(0);
    int rows = records.size();

    for (int i = 0; i < rows; ++i) {
      JSONArray row = records.getJSONArray(i);
      int size = row.size();

      Record record = new Record();

      for (int j = 0; j < size; ++j) {
        JSONObject field = row.getJSONObject(j);
        String title = field.getString("title");

        switch (field.getIntValue("type")) {
          case NUMERIC:
          case FORMULA:
            if (0 == i) {
              sqlCreate.append("`").append(title).append("` DOUBLE, ");
            }

            record.set(title, field.getDoubleValue("value"));
            break;

          case BOOLEAN:
            if (0 == i) {
              sqlCreate.append("`").append(title).append("` BOOLEAN, ");
            }

            record.set(title, field.getBooleanValue("value"));
            break;

          case DATE:
          case DATETIME:
            if (0 == i) {
              sqlCreate.append("`").append(title).append("` DATE, ");
            }

            record.set(title, field.getDate("value"));
            break;

          case ERROR:
          case STRING:
          case BLANK:
          default:
            if (0 == i) {
              sqlCreate.append("`").append(title).append("` VARCHAR(")
                  .append(VARCHAR_SIZE).append("), ");
            }

            record.set(title, field.getString("value"));
            break;
        }

      }

      if (0 == i) {
        sqlCreate.append("PRIMARY KEY (`id`)")
            .append(") ENGINE=InnoDB AUTO_INCREMENT=1")
            .append(" DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        if (overwrite) {
          db.update("DROP TABLE IF EXISTS `" + table + "`");
        }

        db.update(sqlCreate.toString());
      }

      db.save(table, record);
    }
  }

  public boolean toFile(String path) {
    boolean result = false;

    try {
      FileOutputStream out = new FileOutputStream(path);
      workbook.write(out);
      out.flush();
      out.close();
      result = true;
    } catch (Exception e) {
      e.printStackTrace();
    }

    return result;
  }

  private void init(boolean withWorkbook) {
    data = new JSONArray();
    titles = new ArrayList<>();

    picked = new JSONArray();
    selection = new Selection();

    hasParsed = false;

    if (withWorkbook) {
      workbook = new XSSFWorkbook();
      workbook.createSheet("Sheet1");

      // [Neo] .TODO
      CreationHelper helper = workbook.getCreationHelper();

      dateStyle = workbook.createCellStyle();
      dateStyle.setDataFormat(helper.createDataFormat().getFormat(
          Consts.PATTERN_DATE));

      dateTimeStyle = workbook.createCellStyle();
      dateTimeStyle.setDataFormat(helper.createDataFormat().getFormat(
          Consts.PATTERN_DATETIME));
    }
  }

  private Date tryDate(Cell cell) {
    Date date = null;

    if (HSSFDateUtil.isCellDateFormatted(cell)) {
      date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
    }

    return date;
  }

  private boolean parse(DbPro db, String table, String select,
      Object... paras) {
    boolean result = false;

    Sheet sheet = workbook.getSheetAt(0);
    Row titleRow = sheet.createRow(0);

    ArrayList<Title> title = new ArrayList<>();
    titles.add(title);

    int i = 0;
    List<Record> cols = db.find("SHOW COLUMNS FROM `" + table + "`");
    for (Record c : cols) {
      String field = c.getStr("Field");
      title.add(new Title(field, field, c.getStr("Type"), i));

      Cell cell = titleRow.createCell(i);
      cell.setCellType(CellType.STRING);
      cell.setCellValue(field);

      i++;
    }

    if (null == select || select.length() == 0) {
      select = "SELECT * FROM `" + table + "`";
    }

    int rowIndex = 1;
    List<Record> records = db.find(select, paras);

    for (Record r : records) {
      Row row = sheet.createRow(rowIndex++);

      for (Title t : title) {
        Cell cell = row.createCell(t.order);

        switch (t.type) {
          case BOOLEAN:
            cell.setCellValue(r.getBoolean(t.title));
            break;

          case DATE: {
            Date date = r.getDate(t.title);

            if (null != date) {
              cell.setCellStyle(dateStyle);
              cell.setCellValue(HSSFDateUtil.getExcelDate(date));
            }
          }
          break;

          case DATETIME: {
            Date date = r.getDate(t.title);

            if (null != date) {
              cell.setCellStyle(dateTimeStyle);
              cell.setCellValue(HSSFDateUtil.getExcelDate(date));
            }
          }
          break;

          case NUMERIC:
            Double d = r.getDouble(t.title);
            cell.setCellType(t.eType);

            if (null != d) {
              cell.setCellValue(d);
            }
            break;

          default:
            cell.setCellType(t.eType);
            cell.setCellValue(r.getStr(t.title));
            break;
        }
      }
    }

    parse();
    return result;
  }

  private ArrayList<Title> parseTitle(Row r) {
    ArrayList<Title> title = new ArrayList<>();

    if (null != r) {
      int cellSize = r.getLastCellNum();
      for (int i = 0; i < cellSize; ++i) {
        String t = "";
        Cell c = r.getCell(i);

        if (null != c && c.toString().length() > 0) {
          t = c.toString();
        }

        title.add(new Title(t, CellType.STRING, i));
      }
    }

    return title;
  }


  private void parse() {
    if (hasParsed) {
      return;
    }

    data.clear();
    picked.clear();

    // [Neo] 0 ~ size
    int sheetSize = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetSize; ++i) {

      Sheet sheet = workbook.getSheetAt(i);
      if (null == sheet) {
        continue;
      }

      int offset = 0;
      boolean targetSheet = i == selection.sheet;

      if (targetSheet) {
        offset = selection.row;
      }

      ArrayList<Title> title = parseTitle(sheet.getRow(offset++));

      titles.add(title);
      selection.parseTitle(title);

      JSONObject sheetJson = new JSONObject();
      sheetJson.put("sheet", sheet.getSheetName());

      JSONArray rowsJson = new JSONArray();
      sheetJson.put("data", rowsJson);

      int rowSize = sheet.getLastRowNum();
      for (int j = 1; j <= rowSize; ++j) {

        Row row = sheet.getRow(j);
        if (null == row) {
          continue;
        }

        JSONArray rowJson = new JSONArray();

        // [Neo] 0 ~ size
        int cellSize = row.getLastCellNum();
        for (int k = 0; k < cellSize; ++k) {

          Cell cell = row.getCell(k);
          if (null == cell) {
            continue;
          }

          JSONObject cellJson = new JSONObject();
          cellJson.put("row", j);
          cellJson.put("col", k);
          cellJson.put("cell", genCellName(j, k));
          cellJson.put("title", title.get(k).title);
          cellJson.put("type", cell.getCellTypeEnum().ordinal());

          switch (cell.getCellTypeEnum()) {
            case FORMULA:
              cellJson.put("formula", cell.getCellFormula());
            case NUMERIC: {
              Date date = tryDate(cell);

              if (null == date) {
                cellJson.put("value", cell.getNumericCellValue());
              } else {
                cellJson.put("value", date);
                cellJson.put("type", DATE);
              }
            }
            break;

            case STRING:
              cellJson.put("value", cell.getStringCellValue());
              break;

            case BOOLEAN:
              cellJson.put("value", cell.getBooleanCellValue());
              break;

            case ERROR:
              cellJson.put("value", cell.getErrorCellValue());
              break;

            case BLANK:
            default:
              cellJson.put("value", "");
              break;
          }

          rowJson.add(cellJson);
        }

        if (!rowJson.isEmpty()) {
          rowsJson.add(rowJson);

          // [Neo] .TODO picked
          if (targetSheet && j >= offset) {
            picked.add(rowJson);
          }
        }
      }

      data.add(sheetJson);
    }
    hasParsed = true;
  }


  public void addRows(List<Title> title, List<Record> data) {
    hasParsed = false;

    // [Neo] .TODO
    Sheet sheet = workbook.getSheetAt(0);
    Row titleRow = sheet.createRow(0);
    for (Title t : title) {
      Cell c = titleRow.createCell(t.order - 1);
      c.setCellValue(t.title);
    }

    for (Record r : data) {
      int offset = sheet.getLastRowNum() + 1;
      Row row = sheet.createRow(offset);

      for (Title t : title) {
        Cell c = row.createCell(t.order - 1);

        switch (t.type) {
          case FORMULA:
          case NUMERIC: {
            Double d = r.getDouble(t.field);

            if (null != d) {
              c.setCellValue(d);
              c.setCellType(CellType.NUMERIC);
            }
          }
          break;

          case BOOLEAN:
            c.setCellValue(r.getBoolean(t.field));
            c.setCellType(CellType.BOOLEAN);
            break;

          case DATE: {
            Date date = r.getDate(t.field);

            if (null != date) {
              c.setCellStyle(dateStyle);
              c.setCellValue(HSSFDateUtil.getExcelDate(date));
            }
          }
          break;

          case DATETIME: {
            Date date = r.getDate(t.field);

            if (null != date) {
              c.setCellStyle(dateTimeStyle);
              c.setCellValue(HSSFDateUtil.getExcelDate(date));
            }
          }
          break;

          case BLANK:
          case STRING:
          case ERROR:
          default:
            c.setCellValue(r.getStr(t.field));
            break;
        }
      }
    }

    titles.add((ArrayList<Title>) title);
    parse();

  }

  private void addRow(Row row, JSONArray json) {
    hasParsed = false;

    int cols = json.size();
    for (int i = 0; i < cols; ++i) {
      JSONObject col = json.getJSONObject(i);

      int type = NONE;
      try {
        type = col.getIntValue("type");
      } catch (Exception e) {
        // [Neo] .Empty
      }

      Cell cell = row.createCell(i);
      switch (type) {
        case FORMULA:
        case NUMERIC: {
          Double d = col.getDouble("value");

          if (null != d) {
            cell.setCellValue(d);
            cell.setCellType(CellType.NUMERIC);
          }
        }
        break;

        case BOOLEAN:
          cell.setCellValue(col.getBooleanValue("value"));
          cell.setCellType(CellType.BOOLEAN);
          break;

        case DATE: {
          Date date = col.getDate("value");

          if (null != date) {
            cell.setCellStyle(dateStyle);
            cell.setCellValue(HSSFDateUtil.getExcelDate(date));
          }
        }
        break;

        case DATETIME: {
          Date date = col.getDate("value");

          if (null != date) {
            cell.setCellStyle(dateTimeStyle);
            cell.setCellValue(HSSFDateUtil.getExcelDate(date));
          }
        }
        break;

        case BLANK:
        case STRING:
        case ERROR:
        default:
          cell.setCellValue(col.getString("value"));
          break;
      }
    }
  }

  public void addRow(Sheet sheet, int rowIndex, JSONArray json) {
    if (null != sheet) {
      if (-1 == rowIndex) {
        rowIndex = sheet.getLastRowNum();
      }

      if (null != sheet) {
        Row row = sheet.getRow(rowIndex);
        row = sheet.createRow(null == row ? rowIndex : rowIndex + 1);

        addRow(row, json);
        parse();
      }
    }
  }

  public void addRows(Sheet sheet, int rowIndex, JSONArray json) {
    if (null != sheet) {
      if (-1 == rowIndex) {
        rowIndex = sheet.getLastRowNum();
      }

      // [Neo] .TODO add title

      if (null != sheet) {
        int rows = json.size();
        for (int i = 0; i < rows; ++i) {
          Row row = sheet.getRow(rowIndex);
          row = sheet.createRow(null == row ? rowIndex : rowIndex + 1);

          addRow(row, json.getJSONArray(i));
          rowIndex++;
        }

        parse();
      }
    }
  }

//  public void addRow(String sheet, int rowIndex, JSONArray row) {
//    addRow(workbook.getSheet(sheet), rowIndex, row);
//  }
//
//  public void addRow(int sheet, int rowIndex, JSONArray row) {
//    addRow(workbook.getSheetAt(sheet), rowIndex, row);
//  }
//
//  public void addRow(String sheet, JSONArray row) {
//    addRow(workbook.getSheet(sheet), -1, row);
//  }
//
//  public void addRow(int sheet, JSONArray row) {
//    addRow(workbook.getSheetAt(sheet), -1, row);
//  }
//
//  public void addRow(JSONArray row) {
//    addRow(workbook.getSheetAt(0), -1, row);
//  }
//
//  public void addRows(String sheet, int rowIndex, JSONArray rows) {
//    addRows(workbook.getSheet(sheet), rowIndex, rows);
//  }
//
//  public void addRows(int sheet, int rowIndex, JSONArray rows) {
//    addRows(workbook.getSheetAt(sheet), rowIndex, rows);
//  }
//
//  public void addRows(String sheet, JSONArray rows) {
//    addRows(workbook.getSheet(sheet), -1, rows);
//  }
//
//  public void addRows(int sheet, JSONArray rows) {
//    addRows(workbook.getSheetAt(sheet), -1, rows);
//  }
//
//  public void addRows(JSONArray rows) {
//    addRows(workbook.getSheetAt(0), -1, rows);
//  }

  public JSONArray preview() {
    return preview(10);
  }

  public JSONArray preview(int lines) {
    JSONArray result = new JSONArray();

    int sheetSize = data.size();
    for (int i = 0; i < sheetSize; ++i) {
      JSONObject sheetJson = data.getJSONObject(i);
      JSONArray rows = sheetJson.getJSONArray("data");

      JSONArray pRows = new JSONArray();

      int limit = rows.size() > lines ? lines : rows.size();
      for (int j = 0; j < limit; ++j) {
        // [Neo] .clone
        pRows.add(JSONArray.parseArray(rows.getJSONArray(j).toJSONString()));
      }

      JSONObject pSheet = new JSONObject();
      pSheet.put("sheet", sheetJson.getString("sheet"));
      pSheet.put("data", pRows);

      result.add(pSheet);
    }

    return result;
  }

  public JSONArray pick(int sheet, int row) {
    return pick(sheet, row, null);
  }

  public JSONArray pick(int sheet, int row, int[] cols) {
    selection.set(sheet, row, cols);
    parse();

    return picked;
  }

  public JSONArray getData() {
    return data;
  }

  private String genCellName(int row, int index) {
    return Strings.toScale(index, N26.length, N26) + (row + 1);
  }

  private enum ext {
    xls,
    xlsx,
  }

  public static class Title {

    public String title;
    public String field;

    public String dType;
    public CellType eType;

    public int type;
    public int order;

    /**
     * db2e
     */
    public Title(String field, String title, String type, int order) {
      this.title = title;
      this.field = field;

      this.dType = type;
      this.order = order;

      if (null == dType || dType.length() == 0) {
        eType = CellType.STRING;
      } else if (dType.contains("int") ||
          dType.contains("float") ||
          dType.contains("double")) {
        if (dType.equals("tinyint(1)")) {
          eType = CellType.BOOLEAN;
        } else {
          eType = CellType.NUMERIC;
        }
      } else if (dType.contains("bool")) {
        eType = CellType.BOOLEAN;
      } else {
        eType = CellType.STRING;
      }

      this.type = eType.ordinal();

      if (dType.equals("date")) {
        this.type = DATE;
      } else if (dType.equals("datetime")) {
        this.type = DATETIME;
      }

    }

    /**
     * fromFile
     */
    public Title(String title, CellType eType, int order) {
      this.title = title;
      this.field = title;

      this.eType = eType;
      type = eType.ordinal();

      this.order = order;
    }
  }

  private static class Selection {

    int sheet;
    int row;

    ArrayList<Title> title;
    LinkedHashSet<Integer> cols;

    public Selection() {
      sheet = 0;
      row = 0;
    }

    public void set(int sheet, int row, int[] cols) {
      if (sheet < 0) {
        sheet = 0;
      }

      if (row < 0) {
        row = 0;
      }

      this.sheet = sheet;
      this.row = row;

      if (null != cols && cols.length > 0) {
        this.cols = new LinkedHashSet<>();
        for (int c : cols) {
          this.cols.add(c);
        }
      }
    }

    public boolean pick(int c) {
      return (null == cols || cols.contains(c));
    }

    public void parseTitle(ArrayList<Title> title) {
      if (null != title && title.size() > 0) {
        this.title = new ArrayList<>();

        for (Title t : title) {
          if (pick(t.order)) {
            this.title.add(t);
          }
        }
      }
    }

  }
}
